/************************************************************************************/
/*  Program name CAPFNDIMP_MTL_PARA.sql                                             */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 30 NOV, 2005   WONG Wing Kin         First created                               */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;

--ALTER TRIGGER AX_MTL_PARAMETERS_ARDI1 DISABLE;
--ALTER TRIGGER GMF_MTL_PARAMETERS_BIUR_TG DISABLE;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPFNDIMP_MTL_PARA';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_FND_MTL_PARA;

	lv_Next_Seq_ID	NUMBER;
	lv_SOB_SName	VARCHAR2(20);
	lv_COA_ID		NUMBER;

BEGIN

	FOR C1 IN Cur_F LOOP
		BEGIN
			
			BEGIN
				lv_SOB_SName:= CAPBRIMP_COMMON.GetSOBShortName(CAPBRIMP_MAPPING.GetOrgMapping(C1.ORGANIZATION_ID)) ;
			EXCEPTION								
			WHEN NO_DATA_FOUND THEN
				lv_SOB_SName:= CAPBRIMP_COMMON.GetSOBShortNameIO(CAPBRIMP_MAPPING.GetOrgMapping(C1.ORGANIZATION_ID));
			END ;				

			lv_COA_ID		:=	CAPBRIMP_COMMON.GetCOAID(lv_SOB_SName);
	
			INSERT INTO MTL_PARAMETERS
			VALUES
			(
			CAPBRIMP_MAPPING.GetOrgMapping(C1.ORGANIZATION_ID),				--ORGANIZATION_ID,
			C1.LAST_UPDATE_DATE,
			C1.LAST_UPDATED_BY,					--LAST_UPDATED_BY,
			C1.CREATION_DATE,
			CAPBRIMP_COMMON.GetUser(C1.CREATED_NAME),						--CREATED_BY,
			C1.LAST_UPDATE_LOGIN,
			C1.ORGANIZATION_CODE,
			CAPBRIMP_MAPPING.GetOrgMapping(C1.MASTER_ORGANIZATION_ID),		--MASTER_ORGANIZATION_ID,
			C1.PRIMARY_COST_METHOD,
			CAPBRIMP_MAPPING.GetOrgMapping(C1.COST_ORGANIZATION_ID),		--COST_ORGANIZATION_ID,
			C1.DEFAULT_MATERIAL_COST_ID,
			C1.CALENDAR_EXCEPTION_SET_ID,
			C1.CALENDAR_CODE,
			C1.GENERAL_LEDGER_UPDATE_CODE,
			C1.DEFAULT_ATP_RULE_ID,
			C1.DEFAULT_PICKING_RULE_ID,
			C1.DEFAULT_LOCATOR_ORDER_VALUE,
			C1.DEFAULT_SUBINV_ORDER_VALUE,
			C1.NEGATIVE_INV_RECEIPT_CODE,
			C1.STOCK_LOCATOR_CONTROL_CODE,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.MATERIAL_ACC_CCID_D),				--MATERIAL_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.MATERIAL_OVHD_ACC_CCID_D),		--MATERIAL_OVERHEAD_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.MATL_OVHD_ABS_ACC_CCID_D),		--MATL_OVHD_ABSORPTION_ACCT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.RESOURCE_ACC_CCID_D),				--RESOURCE_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.PUR_PRICE_VAR_ACC_CCID_D),		--PURCHASE_PRICE_VAR_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.AP_ACCRUAL_ACC_CCID_D),			--AP_ACCRUAL_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.OVERHEAD_CCID_D),					--OVERHEAD_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.OUTSIDE_PRCESS_ACC_CCID_D),		--OUTSIDE_PROCESSING_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.INTRA_INV_ACC_CCID_D),			--INTRANSIT_INV_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.INTER_RECEIVABLE_ACC_CCID_D),		--INTERORG_RECEIVABLES_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.INTER_PRICE_VAR_ACC_CCID_D),		--INTERORG_PRICE_VAR_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.INTER_PAYABLES_ACC_CCID_D),		--INTERORG_PAYABLES_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.COST_OF_SALES_ACC_CCID_D),		--COST_OF_SALES_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.ENCUMBRANCE_ACC_CCID_D),			--ENCUMBRANCE_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.PROJECT_COST_ACC_CCID_D),			--PROJECT_COST_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.INTER_TRANSFER_CR_ACC_CCID_D),	--INTERORG_TRANSFER_CR_ACCOUNT,
			C1.MATL_INTERORG_TRANSFER_CODE,
			C1.INTERORG_TRNSFR_CHARGE_PERCENT,
			C1.SOURCE_ORGANIZATION_ID,
			C1.SOURCE_SUBINVENTORY,
			C1.SOURCE_TYPE,
			C1.ORG_MAX_WEIGHT,
			C1.ORG_MAX_WEIGHT_UOM_CODE,
			C1.ORG_MAX_VOLUME,
			C1.ORG_MAX_VOLUME_UOM_CODE,
			C1.SERIAL_NUMBER_TYPE,
			C1.AUTO_SERIAL_ALPHA_PREFIX,
			C1.START_AUTO_SERIAL_NUMBER,
			C1.AUTO_LOT_ALPHA_PREFIX,
			C1.LOT_NUMBER_UNIQUENESS,
			C1.LOT_NUMBER_GENERATION,
			C1.LOT_NUMBER_ZERO_PADDING,
			C1.LOT_NUMBER_LENGTH,
			C1.STARTING_REVISION,
			C1.ATTRIBUTE_CATEGORY,
			C1.ATTRIBUTE1,
			C1.ATTRIBUTE2,
			C1.ATTRIBUTE3,
			C1.ATTRIBUTE4,
			C1.ATTRIBUTE5,
			C1.ATTRIBUTE6,
			C1.ATTRIBUTE7,
			C1.ATTRIBUTE8,
			C1.ATTRIBUTE9,
			C1.ATTRIBUTE10,
			C1.ATTRIBUTE11,
			C1.ATTRIBUTE12,
			C1.ATTRIBUTE13,
			C1.ATTRIBUTE14,
			C1.ATTRIBUTE15,
			C1.DEFAULT_DEMAND_CLASS,
			C1.ENCUMBRANCE_REVERSAL_FLAG,
			C1.MAINTAIN_FIFO_QTY_STACK_TYPE,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.INV_PRICE_VAR_ACC_CCID_D),		--INVOICE_PRICE_VAR_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.AVG_COST_VAR_ACC_CCID_D),			--AVERAGE_COST_VAR_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.SALES_ACC_CCID_D),				--SALES_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.EXPENSE_ACC_CCID_D),				--EXPENSE_ACCOUNT,
			C1.SERIAL_NUMBER_GENERATION,
			C1.REQUEST_ID,
			C1.PROGRAM_APPLICATION_ID,
			C1.PROGRAM_ID,
			C1.PROGRAM_UPDATE_DATE,
			C1.GLOBAL_ATTRIBUTE_CATEGORY,
			C1.GLOBAL_ATTRIBUTE1,
			C1.GLOBAL_ATTRIBUTE2,
			C1.GLOBAL_ATTRIBUTE3,
			C1.GLOBAL_ATTRIBUTE4,
			C1.GLOBAL_ATTRIBUTE5,
			C1.GLOBAL_ATTRIBUTE6,
			C1.GLOBAL_ATTRIBUTE7,
			C1.GLOBAL_ATTRIBUTE8,
			C1.GLOBAL_ATTRIBUTE9,
			C1.GLOBAL_ATTRIBUTE10,
			C1.GLOBAL_ATTRIBUTE11,
			C1.GLOBAL_ATTRIBUTE12,
			C1.GLOBAL_ATTRIBUTE13,
			C1.GLOBAL_ATTRIBUTE14,
			C1.GLOBAL_ATTRIBUTE15,
			C1.GLOBAL_ATTRIBUTE16,
			C1.GLOBAL_ATTRIBUTE17,
			C1.GLOBAL_ATTRIBUTE18,
			C1.GLOBAL_ATTRIBUTE19,
			C1.GLOBAL_ATTRIBUTE20,
			C1.MAT_OVHD_COST_TYPE_ID,
			C1.PROJECT_REFERENCE_ENABLED,
			C1.PM_COST_COLLECTION_ENABLED,
			C1.PROJECT_CONTROL_LEVEL,
			C1.AVG_RATES_COST_TYPE_ID,
			C1.TXN_APPROVAL_TIMEOUT_PERIOD,
			C1.MO_SOURCE_REQUIRED,
			C1.MO_PICK_CONFIRM_REQUIRED,
			C1.MO_APPROVAL_TIMEOUT_ACTION,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.BORR_MATL_VAR_ACC_CCID_D),		--BORRPAY_MATL_VAR_ACCOUNT
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.BORR_MOH_VAR_ACC_CCID_D),			--BORRPAY_MOH_VAR_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.BORR_RES_VAR_ACC_CCID_D),			--BORRPAY_RES_VAR_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.BORR_OSP_VAR_ACC_CCID_D),			--BORRPAY_OSP_VAR_ACCOUNT,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.BORR_OVH_VAR_ACC_CCID_D),			--BORRPAY_OVH_VAR_ACCOUNT,
			C1.PROCESS_ENABLED_FLAG,
			C1.PROCESS_ORGN_CODE,
			C1.WSM_ENABLED_FLAG,
			C1.DEFAULT_COST_GROUP_ID,
			C1.LPN_PREFIX,
			C1.LPN_SUFFIX,
			C1.LPN_STARTING_NUMBER,
			C1.WMS_ENABLED_FLAG,
			C1.PREGEN_PUTAWAY_TASKS_FLAG,
			C1.REGENERATION_INTERVAL,
			C1.TIMEZONE_ID,
			C1.MAX_PICKS_BATCH,
			C1.DEFAULT_WMS_PICKING_RULE_ID,
			C1.DEFAULT_PUT_AWAY_RULE_ID,
			C1.DEFAULT_TASK_ASSIGN_RULE_ID,
			C1.DEFAULT_LABEL_COMP_RULE_ID,
			C1.DEFAULT_CARTON_RULE_ID,
			C1.DEFAULT_CYC_COUNT_HEADER_ID,
			C1.CROSSDOCK_FLAG,
			C1.CARTONIZATION_FLAG,
			C1.COST_CUTOFF_DATE,
			C1.ENABLE_COSTING_BY_CATEGORY,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.COST_GROUP_ACC_CCID_D),			--COST_GROUP_ACCOUNTING,
			C1.ALLOCATE_SERIAL_FLAG,
			C1.DEFAULT_PICK_TASK_TYPE_ID,
			C1.DEFAULT_CC_TASK_TYPE_ID,
			C1.DEFAULT_PUTAWAY_TASK_TYPE_ID,
			C1.DEFAULT_REPL_TASK_TYPE_ID,
			C1.EAM_ENABLED_FLAG,
			C1.MAINT_ORGANIZATION_ID,
			C1.PRIORITIZE_WIP_JOBS,
			C1.DEFAULT_CROSSDOCK_SUBINVENTORY,
			C1.SKIP_TASK_WAITING_MINUTES,
			C1.QA_SKIPPING_INSP_FLAG,
			C1.DEFAULT_CROSSDOCK_LOCATOR_ID,
			C1.DEFAULT_MOXFER_TASK_TYPE_ID,
			C1.DEFAULT_MOISSUE_TASK_TYPE_ID,
			C1.DEFAULT_MATL_OVHD_COST_ID,
			C1.DISTRIBUTED_ORGANIZATION_FLAG,
			C1.CARRIER_MANIFESTING_FLAG,
			C1.DISTRIBUTION_ACCOUNT_ID,
			C1.DIRECT_SHIPPING_ALLOWED,
			C1.DEFAULT_PICK_OP_PLAN_ID,
			C1.MAX_CLUSTERS_ALLOWED,
			C1.CONSIGNED_FLAG,
			C1.CARTONIZE_SALES_ORDERS,
			C1.CARTONIZE_MANUFACTURING
			);
			
		EXCEPTION			
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/
--ALTER TRIGGER AX_MTL_PARAMETERS_ARDI1 ENABLE;
--ALTER TRIGGER GMF_MTL_PARAMETERS_BIUR_TG ENABLE;
